package com.sfc.sdsr.dao;

import java.net.URL;
import java.util.UUID;

import com.sfc.sdsr.common.model.Address;
import com.sfc.sdsr.common.model.Event;
import com.sfc.sdsr.common.model.EventAttendance;
import com.sfc.sdsr.common.model.EventRole;
import com.sfc.sdsr.common.model.Key;
import com.sfc.sdsr.common.model.News;
import com.sfc.sdsr.common.model.Occupation;
import com.sfc.sdsr.common.model.Person;
import com.sfc.sdsr.common.model.SfcRole;
import com.sfc.sdsr.common.model.Song;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DataToJava extends SQLiteOpenHelper {

    public DataToJava(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
    
    public Occupation getOccupation (Cursor cursor) {
        int columnIndex = cursor.getColumnIndex(Columns.OCCUPATION_occupation.getName());
        String string = cursor.getString(columnIndex);
        Occupation occupation = Occupation.valueOf(string);
        return occupation;
    }

    public EventRole getEventRole(Cursor cursor) {
        int columnIndex = cursor.getColumnIndex(Columns.EVENT_ROLE_eventRole.getName());
        String string = cursor.getString(columnIndex);
        EventRole eventRole = EventRole.valueOf(string);
        return eventRole;
    }
    
    public SfcRole getSfcRole (Cursor cursor) {
        int columnIndex = cursor.getColumnIndex(Columns.SFC_ROLE_sfcRole.getName());
        String string = cursor.getString(columnIndex);
        SfcRole sfcRole = SfcRole.valueOf(string);
        return sfcRole;
    }

    public Key getKey (Cursor cursor) {
        int columnIndex = cursor.getColumnIndex(Columns.KEY_SIGNATURE_keySignature.getName());
        String string = cursor.getString(columnIndex);
        Key key = Key.valueOf(string);
        return key;
    }

    public Address getAddress(Cursor cursor) {
        Address address = new Address();

        String city = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_city.getName()));
        String commonName = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_commonName.getName()));
        String extraString = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_extraString.getName()));
        String modifier = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_modifier.getName()));
        String state = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_state.getName()));
        String streetName = cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_streetName.getName()));
        UUID addressUuid = UUID.fromString(cursor.getString(cursor.getColumnIndex(Columns.ADDRESS_addressUuid.getName())));
        int houseNumber = cursor.getInt(cursor.getColumnIndex(Columns.ADDRESS_houseNumber.getName()));
        int zip = cursor.getInt(cursor.getColumnIndex(Columns.ADDRESS_zip.getName()));
        
        address.setAddressUuid(addressUuid);
        address.setCity(city);
        address.setCommonName(commonName);
        address.setExtraString(extraString);
        address.setHouseNumber(houseNumber);
        address.setModifier(modifier);
        address.setState(state);
        address.setStreetName(streetName);
        address.setZip(zip);
        return address;
    }

    public Song getSong (Cursor cursor) {
        Song song = new Song();
        
        SQLiteDatabase sld = getReadableDatabase();

        int pk_keySignatureId = cursor.getInt(cursor.getColumnIndex(Columns.SONG_fk_keySignature.getName()));
        String sql = "select * from KeySignature where pk_keySignatureId = " + pk_keySignatureId;
        Cursor innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Key keySignature = getKey(innerCursor);
        innerCursor.close();

        sld.close();

        int bpm = cursor.getInt(cursor.getColumnIndex(Columns.SONG_bpm.getName()));
        String artist = cursor.getString(cursor.getColumnIndex(Columns.SONG_artist.getName()));
        String extraString = cursor.getString(cursor.getColumnIndex(Columns.SONG_extraString.getName()));
        String genra = cursor.getString(cursor.getColumnIndex(Columns.SONG_genra.getName()));
        String title = cursor.getString(cursor.getColumnIndex(Columns.SONG_title.getName()));
        UUID songUuid = UUID.fromString(cursor.getString(cursor.getColumnIndex(Columns.SONG_songUuid.getName())));
        
        song.setArtist(artist);
        song.setBpm(bpm);
        song.setExtraString(extraString);
        song.setGenra(genra);
        song.setKeySignature(keySignature);
        song.setSongUuid(songUuid);
        song.setTitle(title);
        return song;
    }

    public Person getPerson (Cursor cursor) {
        Person person = new Person();
        
        int pk_addressId = cursor.getInt(cursor.getColumnIndex(Columns.PERSON_fk_address.getName()));
        int pk_occupationId = cursor.getInt(cursor.getColumnIndex(Columns.PERSON_fk_occupation.getName()));
        int pk_sfcRoleId = cursor.getInt(cursor.getColumnIndex(Columns.PERSON_fk_sfcRole.getName()));
        
        SQLiteDatabase sld = getReadableDatabase();
        String sql = "select * from Address where pk_addressId = " + pk_addressId;
        Cursor innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Address address = getAddress(innerCursor);
        innerCursor.close();
        
        sql = "select * from Occupation where pk_occupationId = " + pk_occupationId;
        innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Occupation occupation = getOccupation(innerCursor);
        innerCursor.close();
        
        sql = "select * from SfcRole where pk_sfcRoleId = " + pk_sfcRoleId;
        innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        SfcRole sfcRole = getSfcRole(innerCursor);
        innerCursor.close();
        
        sld.close();
        
        String extraString = cursor.getString(cursor.getColumnIndex(Columns.PERSON_extraString.getName()));
        String firstName = cursor.getString(cursor.getColumnIndex(Columns.PERSON_firstName.getName()));
        String lastName = cursor.getString(cursor.getColumnIndex(Columns.PERSON_lastName.getName()));
        String middleName = cursor.getString(cursor.getColumnIndex(Columns.PERSON_middleName.getName()));
        String nickName = cursor.getString(cursor.getColumnIndex(Columns.PERSON_nickName.getName()));
        UUID personUuid = UUID.fromString(cursor.getString(cursor.getColumnIndex(Columns.PERSON_personUuid.getName())));
        Long utcBirthDate = Long.parseLong(cursor.getString(cursor.getColumnIndex(Columns.PERSON_birthDate.getName())));
        Long utcJoinDate = Long.parseLong(cursor.getString(cursor.getColumnIndex(Columns.PERSON_joined.getName())));
        
        person.setAddress(address);
        person.setExtraString(extraString);
        person.setFirstName(firstName);
        person.setLastName(lastName);
        person.setMiddleName(middleName);
        person.setNickName(nickName);
        person.setOccupation(occupation);
        person.setPersonUuid(personUuid);
        person.setSfcRole(sfcRole);
        person.setUtcBirthDate(utcBirthDate);
        person.setUtcJoinDate(utcJoinDate);
        return person;
    }

    public News getNews (Cursor cursor) {
        News news = new News();
        
        int pk_personId = cursor.getInt(cursor.getColumnIndex(Columns.NEWS_fk_author.getName()));
        
        SQLiteDatabase sld = getReadableDatabase();
        String sql = "select * from Person where pk_personId = " + pk_personId;
        Cursor innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Person author = getPerson(innerCursor);
        innerCursor.close();

        sld.close();

        String article = cursor.getString(cursor.getColumnIndex(Columns.NEWS_article.getName()));
        String extraString = cursor.getString(cursor.getColumnIndex(Columns.NEWS_extraString.getName()));
        String headline = cursor.getString(cursor.getColumnIndex(Columns.NEWS_headline.getName()));
        UUID newsUuid = UUID.fromString(cursor.getString(cursor.getColumnIndex(Columns.NEWS_newsUuid.getName())));
        Long utcReportDate = Long.parseLong(cursor.getString(cursor.getColumnIndex(Columns.NEWS_reportDate.getName())));
        
        news.setArticle(article);
        news.setAuthor(author);
        news.setExtraString(extraString);
        news.setHeadline(headline);
        news.setNewsUuid(newsUuid);
        news.setUtcReportDate(utcReportDate);
        return news;
    }

    public Event getEvent(Cursor cursor) {
        Event event = new Event();

        SQLiteDatabase sld = getReadableDatabase();

        int pk_addressId = cursor.getInt(cursor.getColumnIndex(Columns.EVENT_fk_address.getName()));
        String sql = "select * from Address where pk_addressId = " + pk_addressId;
        Cursor innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Address address = getAddress(innerCursor);
        innerCursor.close();

        int pk_personId = cursor.getInt(cursor.getColumnIndex(Columns.EVENT_fk_organizer.getName()));
        sql = "select * from Person where pk_personId = " + pk_personId;
        innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Person organizer = getPerson(innerCursor);
        innerCursor.close();
        
        sld.close();

        String eventName = cursor.getString(cursor.getColumnIndex(Columns.EVENT_eventName.getName()));
        String extraString = cursor.getString(cursor.getColumnIndex(Columns.EVENT_extraString.getName()));
        String reason = cursor.getString(cursor.getColumnIndex(Columns.EVENT_reason.getName()));
        UUID eventUid = UUID.fromString(cursor.getString(cursor.getColumnIndex(Columns.EVENT_eventUuid.getName())));
        Long utcEventDate = Long.parseLong(cursor.getString(cursor.getColumnIndex(Columns.EVENT_eventDate.getName())));
        URL webpage = null;
        
        event.setAddress(address);
        event.setEventName(eventName);
        event.setEventUid(eventUid);
        event.setExtraString(extraString);
        event.setOrganizer(organizer);
        event.setReason(reason);
        event.setUtcEventDate(utcEventDate);
        event.setWebpage(webpage);
        return event;
    }
    
    public EventAttendance getEventAttendance(Cursor cursor) {
        EventAttendance eventAttendance = new EventAttendance();
        
        int pk_eventId = cursor.getInt(cursor.getColumnIndex(Columns.EVENT_ATTENDANCE_fk_event.getName()));
        int pk_eventRoleId = cursor.getInt(cursor.getColumnIndex(Columns.EVENT_ATTENDANCE_fk_role.getName()));
        int pk_personId = cursor.getInt(cursor.getColumnIndex(Columns.EVENT_ATTENDANCE_fk_person.getName()));
        
        SQLiteDatabase sld = getReadableDatabase();
        String sql = "select * from Event where pk_eventId = " + pk_eventId;
        Cursor innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Event event = getEvent(innerCursor);
        innerCursor.close();
        
        sql = "select * from EventRole where pk_eventRoleId = " + pk_eventRoleId;
        innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        EventRole eventRole = getEventRole(innerCursor);
        innerCursor.close();
        
        sql = "select * from Person where pk_personId = " + pk_personId;
        innerCursor = sld.rawQuery(sql, null);
        innerCursor.moveToFirst();
        Person person = getPerson(innerCursor);
        innerCursor.close();

        sld.close();

        eventAttendance.setEvent(event);
        eventAttendance.setEventRole(eventRole);
        eventAttendance.setPerson(person);
        return eventAttendance;
    }

}
